/**
 * 
 */
package co.fitstart.admin.dao;

import java.util.List;

import co.fitstart.entity.coach.PrincipalReportRelation;
import co.fitstart.entity.principal.Principal;
import co.fitstart.entity.principal.PrincipalType;
import co.fitstart.entity.site.Site;

import com.demo2do.core.persistence.annotation.Dynamic;
import com.demo2do.core.persistence.annotation.Execute;
import com.demo2do.core.persistence.annotation.Limit;
import com.demo2do.core.persistence.annotation.Query;
import com.demo2do.core.persistence.annotation.Variable;

/**
 * @author downpour
 *
 */
@Dynamic("hibernate")
public interface PrincipalDao {
    
    /**
     * list principals by page
     * 
     * @param type
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM Principal principal WHERE type = :type AND disabled = false ORDER BY principal.id ASC")
    public List<Principal> list(@Variable("type") PrincipalType type, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param type
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM Principal principal WHERE type = :type AND disabled = false AND financeCode IS NULL ORDER BY principal.id ASC")
    public List<Principal> listEmptyCodePrincipal(@Variable("type") PrincipalType type, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param type
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM Principal principal WHERE type = :type AND disabled = false  AND financeCode IS NOT NULL ORDER BY principal.id ASC")
    public List<Principal> listUnEmptyCodePrincipal(@Variable("type") PrincipalType type, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param type
     * @return
     */
    @Query("FROM Principal principal WHERE type = :type AND disabled = false  ORDER BY principal.id ASC")
    public List<Principal> list(@Variable("type") PrincipalType type);
    
    /**
     * 
     * @param type
     * @return
     */
    @Query("FROM Principal principal WHERE type = :type AND disabled = false ORDER BY principal.id ASC")
    public List<Principal> listAvailable(@Variable("type") PrincipalType type);
    /**
     * get principal by name
     * 
     * @param name
     * @return
     */
    @Query("FROM Principal WHERE disabled = false AND admin = true AND name = :name")
    public Principal get(@Variable("name") String name);
    
    /**
     * list relations for all managers
     * 
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE coach IS NULL ORDER BY id ASC")
    public List<PrincipalReportRelation> listRelation();
    
    /**
     * 
     * @param site
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("FROM PrincipalReportRelation WHERE site = :site AND coach IS NOT NULL ORDER BY id ASC")
    public List<PrincipalReportRelation> listRelation(@Variable("site") Site site, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param sites
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("SELECT relation.manager FROM PrincipalReportRelation relation WHERE relation.site IN (:sites) AND relation.manager IS NOT NULL AND relation.coach IS NULL ORDER BY relation.manager.id ASC")
    public List<Principal> listSiteManager(@Variable("sites") List<Site> sites, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param sites
     * @return
     */
    @Query("SELECT relation.manager FROM PrincipalReportRelation relation WHERE relation.manager.disabled = false AND relation.site IN (:sites) AND relation.manager IS NOT NULL AND relation.coach IS NULL ORDER BY relation.manager.id ASC")
    public List<Principal> listAvailableSiteManager(@Variable("sites") List<Site> sites);
    
    /**
     * 
     * @param sites
     * @return
     */
    @Query("SELECT COUNT(manager) FROM PrincipalReportRelation WHERE site IN (:sites) AND manager IS NOT NULL AND coach IS NULL")
    public Long getSiteManagerCount(@Variable("sites") List<Site> sites);
    
    /**
     * 
     * @param sites
     * @param beginIndex
     * @param maxResult
     * @return
     */
    @Limit
    @Query("SELECT relation.coach FROM PrincipalReportRelation relation WHERE relation.site IN (:sites) AND relation.coach IS NOT NULL ORDER BY relation.coach.id ASC")
    public List<Principal> listCoachAndSiteServer(@Variable("sites") List<Site> sites, @Variable("beginIndex") int beginIndex, @Variable("maxResult") int maxResult);
    
    /**
     * 
     * @param sites
     * @return
     */
    @Query("SELECT relation.coach FROM PrincipalReportRelation relation WHERE relation.coach.disabled = false AND relation.site IN (:sites) AND relation.coach IS NOT NULL ORDER BY relation.coach.id ASC")
    public List<Principal> listAvailableCoachAndSiteServer(@Variable("sites") List<Site> sites);
    
    /**
     * 
     * @param site
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE site = :site AND coach IS NOT NULL ORDER BY id ASC")
    public List<PrincipalReportRelation> listRelation(@Variable("site") Site site);
    
    /**
     * 
     * @param sites
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE site IN (:sites) AND coach IS NOT NULL ORDER BY id ASC")
    public List<PrincipalReportRelation> listRelation(@Variable("sites") List<Site> sites);
    
    /**
     * 
     * @param site
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE site = :site AND coach IS NULL ORDER BY id ASC")
    public List<PrincipalReportRelation> listServerRelation(@Variable("site") Site site);
    
    /**
     * list relations for coach's managers
     * 
     * @param coach
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE coach = :coach AND manager IS NOT NULL")
    public List<PrincipalReportRelation> listRelation(@Variable("coach") Principal coach);
    
    /**
     * 
     * @param coach
     * @param manager
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE coach = :coach AND manager = :manager")
    public PrincipalReportRelation getRelation(@Variable("coach") Principal coach, @Variable("manager") Principal manager);
    
    /**
     * get relation for manager's site or site service's site
     * 
     * @param principal
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE ( coach = :principal AND manager IS NULL ) OR ( manager = :principal AND coach IS NULL )")
    public PrincipalReportRelation getRelation(@Variable("principal") Principal principal);
    
    /**
     * 
     * @param principal
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE ( coach = :principal AND manager IS NULL ) OR ( manager = :principal AND coach IS NULL ) OR ( manager IS NOT NULL AND coach = :principal )")
    public PrincipalReportRelation getSiteRelation(@Variable("principal") Principal principal);
    
    /**
     * 
     * @param managerId
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE manager.id = :managerId AND coach IS NULL")
    public PrincipalReportRelation getRelation(@Variable("managerId") Long managerId);
    
    /**
     * 
     * @param coach
     * @param site
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE coach IS NOT NULL AND manager IS NOT NULL AND coach = :coach AND site = :site")
    public PrincipalReportRelation getRelation(@Variable("coach") Principal coach, @Variable("site") Site site);
    
    /**
     * 
     * @param site
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE coach IS NULL AND manager IS NOT NULL AND site = :site")
    public PrincipalReportRelation getSiteManager(@Variable("site") Site site);
    
    /**
     * 
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE coach IS NULL AND manager IS NOT NULL AND site IS NOT NULL")
    public List<PrincipalReportRelation> listSiteManager();
    
    /**
     * delete principal report relation
     * 
     * @param coach
     */
    @Execute("DELETE FROM PrincipalReportRelation WHERE coach = :coach")
    public void deleteRelation(@Variable("coach") Principal coach);
    
    /**
     * 
     * @param coach
     * @param manager
     */
    @Execute("DELETE FROM PrincipalReportRelation WHERE coach = :coach AND manager = :manager")
    public void deteleRelation(@Variable("coach") Principal coach, @Variable("manager") Principal manager);
    
    /**
     * 
     * @param name
     * @return
     */
    @Query("FROM Principal WHERE disabled = false AND name = :name AND admin = true")
    public Principal load(@Variable("name") String name);
    
    /**
     * 
     * @param coach
     * @return
     */
    @Query("FROM PrincipalReportRelation WHERE manager IS NULL AND coach = :coach")
    public PrincipalReportRelation getBySiteServer(@Variable("coach") Principal coach);
    
    /**
     * 
     * @param manager
     */
    @Execute("DELETE FROM PrincipalReportRelation WHERE manager = :manager AND coach IS NULL")
    public void deleteSiteManager(@Variable("manager") Principal manager);
    
    /**
     * 
     * @param manager
     * @param site
     */
    @Execute("UPDATE PrincipalReportRelation SET manager = :manager WHERE site = :site AND manager IS NOT NULL")
    public void updateSiteManager(@Variable("manager") Principal manager, @Variable("site") Site site);
}
